package connectivity;

import java.lang.Integer;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Category;
import model.DayRevenue;
import model.Product;
import model.Klant;
import model.Order;
import model.OrderRule;
import model.SoldProduct;
import model.User;

public class QueryManager {

    private final Dbmanager dbmanager;

    public QueryManager(Dbmanager dbmanager) {
        this.dbmanager = dbmanager;
    }

    public String getCategoryName(int categoryId) {
        String categoryName = "";
        try {
            String sql = "SELECT naam FROM categorie WHERE categorie_id='" + categoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                categoryName = result.getString("naam");
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categoryName;
    }

    public List<Category> getCategories() {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM categorie ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Category category = new Category(result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"));
                categories.add(category);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categories;
    }

    public Product getProduct(int productId) {
        Product product = new Product();
        try {
            String sql = "SELECT * FROM product WHERE product_id='" + productId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                product = new Product(result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"),
                        result.getInt("voorraad"),
                        result.getByte("status"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return product;
    }
    
    public User getUser(String username) {
        User user = null;
        try {
            String sql = "SELECT * FROM user WHERE username='" + username + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                user = new User(result.getString("username"),
                        result.getString("password"),
                        result.getInt("AccesLevel"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return user;
    }
    
    public List<User> getUsers() {
 
        String sql = "SELECT * FROM user";
       
        List<User> users = new LinkedList();
        
        ResultSet result = dbmanager.doQuery(sql);
        
        try {
            while(result.next()) {
             
                users.add(new User(
                       result.getString("username"), 
                       result.getString("password"),
                       result.getInt("AccesLevel")
                ));
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(QueryManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return users;
    }
    
    public void addUser(model.User user) {
       
        String sql = "INSERT INTO `user` (username, password, AccesLevel) VALUES('" + user.getUsername() + "', '"+ user.getPassword() +"', '"+ user.getAccesLevel() + "')";
      
        try {
           //dbmanager.executeQuery(sql);
           dbmanager.insertQuery(sql);
            //result.next();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
           

    }
    
    public void removeUser(String username) {
        String sql = "DELETE FROM user WHERE username='" + username + "'";
        
         try {
           //dbmanager.executeQuery(sql);
           dbmanager.removeQuery(sql);
            //result.next();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    
    public void editUser(model.User user, String username) {
       
        String sql = "UPDATE user SET username='"+ user.getUsername() +"',password='"+ user.getPassword() +"', AccesLevel='" + user.getAccesLevel() +"' WHERE username='"+ username +"'";
        
        //String sql = "UPDATE klant SET naam='Hendrika',adres='Test', postcode='TEST', woonplaats='TESt' WHERE klant_id=1";
        
        try {
           //dbmanager.executeQuery(sql);
           dbmanager.updateQuery(sql);
            //result.next();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
            //
            
            //String sql = "UPDATE klant SET naam='Hendrika',adres='Test', postcode='TEST', woonplaats='TESt' WHERE klant_id=1";
            //dbmanager.executeQuery(sql);
        
            System.out.println(user.getUsername());
        
    }
    
    public void updateOrderRule(OrderRule orderRule)
    {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE `orderregel` ")
                .append("SET `orderregel`.`aantal` = ")
                .append(orderRule.getAmount())
                .append(", `orderregel`.`prijs` = ")
                .append(orderRule.getPrice())
                .append("WHERE `orderregel`.`order_id` = ")
                .append(orderRule.getOrderId())
                .append(" AND `orderregel`.`product_id`= ")
                .append(orderRule.getProduct().getProductId())
                .append(" LIMIT 1");

        dbmanager.updateQuery(sb.toString());
    }
    
    public Klant getKlant(int klant_id) {
        Klant klant = null;
        try {
            String sql = "SELECT * FROM klant WHERE klant_id='" + klant_id + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                klant = new Klant(result.getInt("klant_id"),
                        result.getString("naam"),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return klant;
    }
 

    public void removeCustomer(int klantid) {
        String sql = "DELETE FROM klant WHERE klant_id=" + klantid + "";
        
         try {
           //dbmanager.executeQuery(sql);
           dbmanager.removeQuery(sql);
            //result.next();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    
    public void editCostumer(model.Klant klant) {
       
        String sql = "UPDATE klant SET naam='"+ klant.getNaam() +"',adres='"+ klant.getAdres() +"', postcode='" + klant.getPostcode() + "', woonplaats='" + klant.getWoonplaats() + "' WHERE klant_id="+ klant.getKlant_id() +"";
        
        //String sql = "UPDATE klant SET naam='Hendrika',adres='Test', postcode='TEST', woonplaats='TESt' WHERE klant_id=1";
        
        try {
           //dbmanager.executeQuery(sql);
           dbmanager.updateQuery(sql);
            //result.next();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
            //
            
            //String sql = "UPDATE klant SET naam='Hendrika',adres='Test', postcode='TEST', woonplaats='TESt' WHERE klant_id=1";
            //dbmanager.executeQuery(sql);
        
            System.out.println(klant.getKlant_id());
        
    }
    
    public void addCostumer(model.Klant klant) {
       
        String sql = "INSERT INTO `klant` (naam, adres, postcode, woonplaats) VALUES('" + klant.getNaam() + "', '"+ klant.getAdres() +"', '"+ klant.getPostcode() +"', '" + klant.getWoonplaats() + "')";
      
        try {
           //dbmanager.executeQuery(sql);
           dbmanager.insertQuery(sql);
            //result.next();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
           

    }
    
    public List<Klant> getCustomers() {
 
        String sql = "SELECT * FROM klant";
       
        List<Klant> klanten = new LinkedList();
        
        ResultSet result = dbmanager.doQuery(sql);
        
        try {
            while(result.next()) {
             
                klanten.add(new Klant(
                       result.getInt("klant_id"), 
                       result.getString("naam"),
                       result.getString("adres"),
                       result.getString("postcode"),
                       result.getString("woonplaats")
                ));
 
            }
        } catch (SQLException ex) {
            Logger.getLogger(QueryManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return klanten;
    }
    
    public List<Product> getAllProducts() {
        List<Product> products = new LinkedList<Product>();
        try {
            String sql = "SELECT * FROM product";
            ResultSet result = dbmanager.doQuery(sql);
            while(result.next()) {
                products.add(new Product(result.getInt("product_id"), 
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"),
                        result.getInt("voorraad"), 
                        result.getByte("status")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        
        return products;
    }
    
    public void updateProduct(Product product) {
              /*StringBuilder pr = new StringBuilder();
        sb.append("UPDATE `orderregel` ")
                .append("SET `orderregel`.`aantal` = ")
                .append(orderRule.getAmount())
                .append(", `orderregel`.`prijs` = ")
                .append(orderRule.getPrice())
                .append("WHERE `orderregel`.`order_id` = ")
                .append(orderRule.getOrderId())
                .append(" AND `orderregel`.`product_id`= ")
                .append(orderRule.getProduct().getProductId())
                .append(" LIMIT 1");*/
        
        
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE `product` ")
                .append("SET `product`.`categorie_id` = ")
                .append(product.getCategorieId())
                .append(", `product`.`naam` = '")
                .append(product.getName()) 
                .append("', `product`.`prijs` = ")
                .append(product.getPrice())
                .append(", `product`.`omschrijving` = '")
                .append(product.getDescription())
                .append("', `product`.`voorraad` = ")
                .append(product.getVoorraad())
                .append(" WHERE `product`.`product_id` = ")
                .append(product.getProductId())
                .append(" LIMIT 1");
        
        try {
           dbmanager.updateQuery(sb.toString());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    
    public void addProduct(model.Product product) {
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO `product` (categorie_id, naam, prijs, omschrijving, voorraad)" )
                .append("VALUES(")
                .append(product.getCategorieId())
                .append(", '")
                .append(product.getName())
                .append("', ")
                .append(product.getPrice())
                .append(", '")
                .append(product.getDescription())
                .append("', ")
                .append(product.getVoorraad())
                .append(")");
       
       
       // String sql =  "VALUES('" + product.getCategorieId() + "', '"+ product.getName() +"', '"+ product.getPrice() +"', '" + product.getDescription() + product.getVoorraad() + "')";
        try {
           dbmanager.insertQuery(sb.toString());
           
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    
    public void updateProductVoorraad(int bestelling, int id) {
      String sql = "UPDATE product SET voorraad = (voorraad - " + bestelling + ") WHERE product_id = " + id ;
        try {
           dbmanager.insertQuery(sql);
           
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    
    public void removeProduct(int id) {
        String sql = "UPDATE product set status = 0 WHERE product_id = " + id;
        try {
           dbmanager.insertQuery(sql);
           
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    
    public void removeProduct(String username) {
        String sql = "DELETE FROM user WHERE username='" + username + "'";
        
         try {
           //dbmanager.executeQuery(sql);
           dbmanager.removeQuery(sql);
            //result.next();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    

    public List<Product> getProducts(int categoryId) {
        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT * FROM product WHERE categorie_id='" + categoryId + "' ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"),
                        result.getInt("voorraad"),
                        result.getByte("status")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }

    public void setOrder(model.Basket basket, String naam, String adres,
            String postcode, String woonplaats, String opmerking, String betaalmethode) {
        String SQL_order = "INSERT INTO `order` (naam, adres, postcode, woonplaats, notes, betaalmethode, datum)"
                + " VALUES('" + naam + "', '" + adres + "', '" + postcode + "', '"
                + woonplaats + "', '" + opmerking + "', '" + betaalmethode + "', CURDATE() )";
        int order_id = 0;
        try {
            ResultSet result = dbmanager.insertQuery(SQL_order);
            result.next();
            order_id = result.getInt(1);
        } catch (SQLException e) {
            System.out.println("connectivity.QueryManager.setOrder() Exception:" + e.getMessage());
        }
        List<Product> products = basket.getProducts();
        for (Product product : products) {
            int product_id = product.getProductId();
            int aantal = basket.getProductAmount(product);
            double product_prijs = product.getPrice();
            String SQL_orderProduct = "INSERT INTO orderregel (product_id,order_id,aantal,prijs) VALUES (" + product_id + 
                    "," + order_id + "," + aantal + ", " + product_prijs + ")";
            dbmanager.insertQuery(SQL_orderProduct);
        }
    }
    
    public void removeOrder(int order_id) {
        // Delete orderrules
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM `orderregel` WHERE `order_id` = ");
        sb.append(order_id);
        dbmanager.removeQuery(sb.toString());
        
        sb = new StringBuilder();
        sb.append("DELETE FROM `order` WHERE `order_id` = ");
        sb.append(order_id);
        dbmanager.removeQuery(sb.toString());
    }
    
    public OrderRule getOrderRule(int orderId, int productId)
    {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT `product`.*, `orderregel`.`order_id`, `orderregel`.`aantal`, `orderregel`.`prijs` ")
                .append("FROM `orderregel` ")
                .append("LEFT JOIN `product` ")
                .append("ON `product`.`product_id` = `orderregel`.`product_id` ")
                .append("WHERE `orderregel`.`order_id` =")
                .append(orderId)
                .append(" AND `orderregel`.`product_id` = ")
                .append(productId)
                .append(" LIMIT 1");
                
        ResultSet resultSet = dbmanager.doQuery(sb.toString());
        
        OrderRule orderRule = null;
        
        try
        {
            if (resultSet.next())
            {
                orderRule = new OrderRule(
                        resultSet.getInt("order_id"),
                        new Product(
                            resultSet.getInt("product_id"),
                            resultSet.getInt("categorie_id"),
                            resultSet.getString("naam"),
                            resultSet.getString("omschrijving"),
                            resultSet.getDouble("prijs"),
                            resultSet.getInt("voorraad"),
                            resultSet.getByte("status")
                        ),
                        resultSet.getInt("aantal"),
                        resultSet.getDouble("prijs")
                    );
            }
        }
        catch (SQLException e)
        {
            System.out.println("QueryManager.getOrderRule: " + e.getMessage());
        }
        
        return orderRule;
    }
    
    public List<OrderRule> getOrderRules(int order_id) {
        List<OrderRule> orderRules = new LinkedList<OrderRule>();
        
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT `orderregel`.`product_id`, `product`.`naam`, ");
        sb.append("`orderregel`.`order_id`, `orderregel`.`aantal`, ");
        sb.append("`orderregel`.`prijs` ");
        sb.append("FROM `orderregel` ");
        sb.append("LEFT JOIN `product` ");
        sb.append("ON `orderregel`.`product_id` = `product`.`product_id` ");
        sb.append("WHERE `order_id` = ");
        sb.append(order_id);
        sb.append(" ORDER BY `orderregel`.`product_id` ASC");
        
        ResultSet resultSet = dbmanager.doQuery(sb.toString());
        
        try {
            while (resultSet.next()) {
                Product product = new Product();
                product.setProductId(resultSet.getInt("product_id"));
                product.setName(resultSet.getString("naam"));
                
                orderRules.add(new OrderRule(
                        resultSet.getInt("order_id"),
                        product,
                        resultSet.getInt("aantal"),
                        resultSet.getDouble("prijs")
                ));
            }
        } catch (SQLException e) {
            System.out.println("QueryManager.getOrderRules: " + e.getMessage());
        }
        
        return orderRules;
    }
    
    public List<Order> getOrders()
    {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT order_id, naam, adres, postcode, woonplaats, notes, betaalmethode, datum ");
        sb.append("FROM `order`");
        sb.append("ORDER BY `datum` DESC");
        
        List<Order> orders = new LinkedList<Order>();
        
        ResultSet resultSet = dbmanager.doQuery(sb.toString());
        
        try
        {
            while (resultSet.next())
            {
                orders.add(new Order(
                        resultSet.getInt("order_id"),
                        resultSet.getString("naam"),
                        resultSet.getString("adres"),
                        resultSet.getString("postcode"),
                        resultSet.getString("woonplaats"),
                        resultSet.getString("notes"),
                        resultSet.getString("betaalmethode"),
                        resultSet.getDate("datum")
                    ));
            }
        }
        catch (SQLException e)
        {
            System.out.println("QueryManager.getOrders: " + e.getMessage());
        }
        
        return orders;
    }
    
    public Map<Product, Integer> getSoldProducts()
    {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT `orderregel`.`aantal`, `product`.`product_id`, `product`.`naam`, `product`.`prijs` ");
        sb.append("FROM `orderregel` ");
        sb.append("LEFT JOIN `product` ");
        sb.append("ON `orderregel`.`product_id` = `product`.`product_id` ");
        
        Map<Product, Integer> soldProducts = new HashMap<Product, Integer>();
        
        ResultSet resultSet = dbmanager.doQuery(sb.toString());
        
        try
        {
            while (resultSet.next())
            {
                Product p = new Product();
                p.setProductId(resultSet.getInt("product_id"));
                p.setName(resultSet.getString("naam"));
                p.setPrice(resultSet.getDouble("prijs"));
                
                int amount = resultSet.getInt("aantal");
                
                if (soldProducts.containsKey(p))
                    amount = soldProducts.get(p) + amount;

                soldProducts.put(p, amount);
            }
        }
        catch (SQLException e)
        {
            System.out.println("QueryManager.getSoldProducts: " + e.getMessage());
        }
        
        return soldProducts;
    }

    public List<DayRevenue> getRevenueBetween(String fromDate, String toDate) {
        List<DayRevenue> revenue = new LinkedList<DayRevenue>();
        
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT `order`.`datum`, SUM(`orderregel`.`aantal` * `orderregel`.`prijs`) as dayrevenue ");
        sb.append("FROM `orderregel` ");
        sb.append("LEFT JOIN `order` ");
        sb.append("ON `order`.`order_id` = `orderregel`.`order_id` ");
        sb.append("WHERE `order`.`datum` BETWEEN '");
        sb.append(fromDate);
        sb.append("' AND '");
        sb.append(toDate);
        sb.append("' GROUP BY `order`.`datum` ");
        sb.append(" ORDER BY `order`.`datum` ");
            
        ResultSet rs = dbmanager.doQuery(sb.toString());
        
        try {
            while(rs.next()) {
                DayRevenue dr = new DayRevenue(rs.getDate("datum"),
                        rs.getDouble("dayrevenue"));
                revenue.add(dr);
            }
        } catch(SQLException e) {
            System.err.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        
        return revenue;
    }
    
    /**
     * @param start The date to start from, this is a UNIX timestamp
     * @param end The date to end with, this is a UNIX timestamp
     * @return 
     */
    public List<SoldProduct> getSoldProductsBetween(long start, long end)
    {
        if (start < 0 || end < 0)
        {
            throw new IllegalArgumentException(
                    "Can't go back in time, " +
                    "either start or end is negative"
                );
        }
        
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SUM(`orderregel`.`aantal`) as total, `product`.`product_id`, `product`.`naam`, `product`.`prijs` ");
        sb.append("FROM `orderregel` ");
        sb.append("LEFT JOIN `order` ");
        sb.append("ON `order`.`order_id` = `orderregel`.`order_id` ");
        sb.append("LEFT JOIN `product` ");
        sb.append("ON `orderregel`.`product_id` = `product`.`product_id` ");
        sb.append("WHERE UNIX_TIMESTAMP(`order`.`datum`) BETWEEN ");
        sb.append(start);
        sb.append(" AND ");
        sb.append(end);
        sb.append(" GROUP BY `orderregel`.`product_id` ");
        sb.append(" ORDER BY total DESC");
        
        List<SoldProduct> soldProducts = new LinkedList<SoldProduct>();
        
        ResultSet resultSet = dbmanager.doQuery(sb.toString());
        
        try
        {
            while (resultSet.next())
            {
                Product p = new Product();
                p.setProductId(resultSet.getInt("product_id"));
                p.setName(resultSet.getString("naam"));
                p.setPrice(resultSet.getDouble("prijs"));
                
                int amount = resultSet.getInt("total");
                
                soldProducts.add(new SoldProduct(p, amount));
            }
        }
        catch (SQLException e)
        {
            System.out.println("QueryManager.getSoldProductsBetween: " + e.getMessage());
        }        
        
        return soldProducts;
    }
}
